/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean;

import bean.ExamMark;
import bean.Connect;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Alex
 */
public class ExamMarkBean {

    ArrayList<ExamMark> listEXam;

    Connection con = Connect.getConnection();

    public ExamMarkBean() {
    }

    public ArrayList<ExamMark> getListExamMark() {
        listEXam = new ArrayList<ExamMark>();

        try {
            String sql = "select b.stuID, b.stuName, c.suName, a.maDate, a.maMark from Marks a, Students b, Subjects c" + 
                    " where a.maStudent=b.stuID and c.suID=a.maSubject ";
            PreparedStatement stm = con.prepareStatement(sql);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                ExamMark exam = new ExamMark();
                exam.setStuId(rs.getInt("stuID"));
                exam.setStuName(rs.getString("stuName"));
                exam.setSuName(rs.getString("suName"));
                exam.setMaDate(rs.getString("maDate"));
                exam.setMaMark(rs.getFloat("maMark"));
                listEXam.add(exam);
            }
            rs.close();
            con.close();
            return listEXam;
        } catch (Exception ex) {
            System.out.println(ex);


        }
        return null;
    }

    public boolean InsertExamMark(int stuID, int suID,float maMark, String maDate) {
        Connect ojb=new Connect();
        Connection con=ojb.getconnection();
        try{
            String sql="insert into [Marks](stuID, suID, maMark, maDate) values(?,?,?,?)";
            PreparedStatement stm=con.prepareStatement(sql);
            stm.setInt(1, stuID);
            stm.setInt(2, suID);
            stm.setFloat(3, maMark);
            stm.setString(4, maDate);
            int result=stm.executeUpdate();
            stm.close();
            con.close();
            if(result>0)
                return true;

        }catch(Exception ex){
            ex.printStackTrace();
        }
        return false;
    }

    public boolean deleteExamMark(int id){
        Connect ojb=new Connect();
        Connection con=ojb.getconnection();
        try{
            String sql="Delete from Marks where maID=?";
            PreparedStatement stm=con.prepareStatement(sql);
            stm.setInt(1, id);
            int result=stm.executeUpdate();
            stm.close();
            con.close();
            if(result>0)
                return true;
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return false;
    }

    public boolean updateMark(int stuID, int suID,float maMark, String maDate){
        Connect ojb=new Connect();
        Connection con=ojb.getconnection();
        try{
            String sql="update Marks set stuID=?, suID=?, maMark=?, maDate=?";
            PreparedStatement stm=con.prepareStatement(sql);
            stm.setInt(1, stuID);
            stm.setInt(2, suID);
            stm.setFloat(4, maMark);
            stm.setString(5, maDate);
            int result=stm.executeUpdate();
            stm.close();
            con.close();
            if(result>0)
                return true;
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return false;
    }
}
